iT邦幫忙

第 11 屆 iThome 鐵人賽
DAY 5
2
Software Development

從問題理解與活用SQL語法系列 第 5 篇

第五堂：家事管理 - 每日家事分工一覽表 (SELECT 子查詢)

11th鐵人賽
3653 瀏覽

  • 分享至 

  • xImage
    •  

一、回顧：第四堂　情境練習題

(一)題目

列出員工資料表(employees)當中的所有員工的「完整姓名(Last Name + First Name)」、「出生年月日 (BirthDate)」，並計算每個人活到2222-11-22的年齡實歲 (age)

https://ithelp.ithome.com.tw/upload/images/20190922/20120331t9OcoIiLWp.png

(二)參考答案

SQL Server 參考SQL作法

作答網址：
https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_datediff

SELECT CONCAT(LastName,' ' ,FirstName) AS FullName,
	   BirthDate,
	   FLOOR(DATEDIFF(day, BirthDate, CAST('2222-11-22' AS DATE)) / 365.25 ) AS age
FROM Employees

MySQL 參考SQL作法

作答網址：
https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

SELECT CONCAT(LastName,' ' ,FirstName) AS FullName,
	   BirthDate,
	   FLOOR(DATEDIFF(CAST('2222-11-22' AS DATE), BirthDate) / 365.25 ) AS age
FROM Employees

(三)講解

  1. 出生日 ~ 2222-11-22的天數相差：使用DATEDIFF。

因為'2222-11-22'是字串，所以要轉成DATE格式，才能使用日期函式日期進行計算

SELECT DATEDIFF(day, BirthDate, CAST('2222-11-22' AS DATE)) as "出生到2222-11-22的經過天數"
FROM Employees;
相差天數
92755
98892
94682
96488
97783
94741
95870
96741
  1. 天數除以一年365.25天(考慮4年一閏年)，去掉小數點：使用FLOOR。
SELECT DATEDIFF(day, BirthDate, CAST('2222-11-22' AS DATE)) / 365.25 as floatAge,
       FLOOR(DATEDIFF(CAST('2222-11-22' AS DATE), BirthDate) / 365.25 ) AS age
FROM Employees
floatAge age
253.9493 253
270.7515 270
259.2252 259
264.1697 264
267.7153 267
259.3867 259
262.4778 262
264.8624 264
  1. Full Name = Last Name 與 First Name 兩個部分相接：使用　CONCAT
SELECT CONCAT(LastName,' ' ,FirstName) AS FullName,
	   FLOOR(DATEDIFF(day, BirthDate, CAST('2222-11-22' AS DATE)) / 365.25 ) AS age
FROM Employees
FullName LastName FirstName
Davolio Nancy Davolio Nancy
Fuller Andrew Fuller Andrew
Leverling Janet Leverling Janet
Peacock Margaret Peacock Margaret
Buchanan Steven Buchanan Steven
Suyama Michael Suyama Michael
King Robert King Robert
Callahan Laura Callahan Laura
Dodsworth Anne Dodsworth Anne

二、第五堂範例：實作每日家事分工一覽表

(一) 需求

列出「每一天」分別負責「打掃」、「拖地」、「洗碗」和「倒垃圾」的家庭成員姓名

(二) 實作結果

https://ithelp.ithome.com.tw/upload/images/20190922/2012033161NNd2F5df.png

SELECT DISTINCT  
       CAST(AllCleanScheduleDateTable.CleanDateTime AS DATE) AS 打掃日期 ,
       ( SELECT ( SELECT FamilyName
                 FROM Family
                 WHERE FamilyId = SubQueryTable.FamilyId)
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
               SubQueryTable.CleanItem = '0') AS  當日打掃成員,
       ( SELECT ( SELECT FamilyName
                  FROM Family
                  WHERE FamilyId = SubQueryTable.FamilyId)
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
               SubQueryTable.CleanItem = '1') AS  當日拖地成員,
       ( SELECT ( SELECT FamilyName
                  FROM Family
                  WHERE FamilyId = SubQueryTable.FamilyId)
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
               SubQueryTable.CleanItem = '2') AS  當日洗碗成員,
       ( SELECT ( SELECT FamilyName
                  FROM Family
                  WHERE FamilyId = SubQueryTable.FamilyId)
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
               SubQueryTable.CleanItem = '3') AS  當日倒垃圾成員
FROM CleanSchedule as AllCleanScheduleDateTable
ORDER BY 打掃日期;

(二) 資料來源

家事排班表(CleanSchedule)：

  1. CleanDateTime：打掃日期
  2. FamilyId：打掃的家庭成員ID(對應到家庭成員資料表 Family 的 FamilyId 欄位)
  3. CleanItem：0代表掃地 1代表拖地 2代表洗碗 3代表倒垃圾
CleanDateTime FamilyId CleanItem
2019-08-17 00:00:00 3ad93ba4-c799-4a32-ac2e-8abc74dd6375 1
2019-08-17 00:00:00 91dcde4b-10b3-421e-ab8e-bb6bc23b4350 2
2019-08-15 00:00:00 91dcde4b-10b3-421e-ab8e-bb6bc23b4350 4
2019-08-18 00:00:00 34bf1b6f-191d-40e9-9a8c-3c282e6a700d 2
2019-08-16 00:00:00 91b18f1f-4ef8-4066-97c4-28daea585db5 0
2019-08-15 00:00:00 bce0e4ae-ac70-4131-aa6f-d1e25b87fad9 3
2019-08-17 00:00:00 34bf1b6f-191d-40e9-9a8c-3c282e6a700d 0
2019-08-18 00:00:00 3ad93ba4-c799-4a32-ac2e-8abc74dd6375 1
2019-08-15 00:00:00 3ad93ba4-c799-4a32-ac2e-8abc74dd6375 5
2019-08-15 00:00:00 de8cb5db-2061-4d35-a662-ba5f528fadba 0
2019-08-17 00:00:00 91b18f1f-4ef8-4066-97c4-28daea585db5 5
2019-08-16 00:00:00 de8cb5db-2061-4d35-a662-ba5f528fadba 1
2019-08-17 00:00:00 de8cb5db-2061-4d35-a662-ba5f528fadba 4
2019-08-17 00:00:00 bce0e4ae-ac70-4131-aa6f-d1e25b87fad9 3
2019-08-18 00:00:00 bce0e4ae-ac70-4131-aa6f-d1e25b87fad9 5
2019-08-18 00:00:00 91dcde4b-10b3-421e-ab8e-bb6bc23b4350 4
2019-08-16 00:00:00 3ad93ba4-c799-4a32-ac2e-8abc74dd6375 3
2019-08-15 00:00:00 91b18f1f-4ef8-4066-97c4-28daea585db5 2
2019-08-15 00:00:00 34bf1b6f-191d-40e9-9a8c-3c282e6a700d 1
2019-08-16 00:00:00 bce0e4ae-ac70-4131-aa6f-d1e25b87fad9 5
2019-08-18 00:00:00 91b18f1f-4ef8-4066-97c4-28daea585db5 3
2019-08-18 00:00:00 de8cb5db-2061-4d35-a662-ba5f528fadba 0
2019-08-16 00:00:00 91dcde4b-10b3-421e-ab8e-bb6bc23b4350 2
2019-08-16 00:00:00 34bf1b6f-191d-40e9-9a8c-3c282e6a700d 4

家庭資料表(Family)：

| FamilyId | FamilyName |
------------------------- |
| 34bf1b6f-191d-40e9-9a8c-3c282e6a700d | 泰肝 |
| 3ad93ba4-c799-4a32-ac2e-8abc74dd6375 | 泰熱 |
| 91b18f1f-4ef8-4066-97c4-28daea585db5 | 泰胖 |
| 91dcde4b-10b3-421e-ab8e-bb6bc23b4350 | 泰瘦 |
| bce0e4ae-ac70-4131-aa6f-d1e25b87fad9 | 泰冷 |
| de8cb5db-2061-4d35-a662-ba5f528fadba | 泰賢 |

三、準備資料

模擬演練平台：DB Fiddle

https://www.db-fiddle.com/

因為SQL Fiddle 最近不穩定，改用 MySQL 5.6 的 DB Fiddle

  1. 將下列建立表格與產生資料的SQL，貼到DB Fiddle左方，接著按下上方的「Run」

https://ithelp.ithome.com.tw/upload/images/20190922/20120331ciEFLEqC0O.png

  1. 在右方輸入要查詢的SQL，輸入完後一樣按下上方的「Run」執行查詢。

https://ithelp.ithome.com.tw/upload/images/20190922/201203310Tkf6oAZHI.png

範例資料SQL

/* 家庭排班表 MySQL範例資料SQL */

CREATE TABLE `CleanSchedule` (
  `CleanScheduleId` varchar(100) NOT NULL,
  `CleanDateTime` datetime DEFAULT NULL,
  `FamilyId` varchar(100) DEFAULT NULL,
  `CleanItem` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`CleanScheduleId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `CleanSchedule` (`CleanScheduleId`, `CleanDateTime`, `FamilyId`, `CleanItem`) VALUES
	('0FF1B602-580C-4416-AF25-7C0876EEE19D', '2019-08-17 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('1506BC60-C614-4643-B950-8D5F803C13D6', '2019-08-17 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('24653640-14FA-49A2-AC46-DB344719A88B', '2019-08-15 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('2D682CA3-B900-41B0-9AD9-5611296DFBE5', '2019-08-18 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2'),
	('34931A84-85E7-4236-B1C4-01D190EEFE27', '2019-08-16 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '0'),
	('380A9221-4A2D-41A3-A350-D53175B670C0', '2019-08-15 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '3'),
	('39832DFE-B93F-45D9-99C2-5EEAE76FB5F3', '2019-08-17 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '0'),
	('4B4E212B-C40B-4233-B3B3-3531D6FE7915', '2019-08-18 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('4C534822-2091-4D9F-94FB-FDCB6568E325', '2019-08-15 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '5'),
	('61ED1DE9-5523-4FBA-B5AA-236007479849', '2019-08-15 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('67359081-D70B-4DCE-8011-B796B7516CE3', '2019-08-17 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '5'),
	('71CBD806-D0E4-4E87-8ACF-1BF9995EF69C', '2019-08-16 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '1'),
	('77B08241-1BEA-4550-AD28-B10CC4E39E35', '2019-08-17 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '4'),
	('78E9F96C-4B60-4ACB-910B-AC6E48AE0270', '2019-08-17 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '3'),
	('7BE1E533-FDE3-40EA-9A4F-E7638BA1E168', '2019-08-18 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '5'),
	('83595DE1-12FE-453D-9DDD-6ED7D0A355F4', '2019-08-18 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('843D27D9-CB11-446B-AF65-563C641D872B', '2019-08-16 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '3'),
	('9743E8D4-596A-4F0F-85B1-9533ED193784', '2019-08-15 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2'),
	('C5DDA457-9C99-42A1-B9AD-271C9DD974F9', '2019-08-15 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '1'),
	('C78F99F6-37B6-4FD5-AD04-D586BF032D1C', '2019-08-16 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '5'),
	('D420FFDD-6BAE-435F-85AE-57D917EA63ED', '2019-08-18 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '3'),
	('F802E6A4-5F10-4985-BFD2-0D5C4435EF6A', '2019-08-18 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('FA6FC631-97B1-4A9E-807D-2B520ACB7D28', '2019-08-16 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('FF257219-DB3E-4CBB-8E63-C5A85B09950A', '2019-08-16 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '4');

/* 家庭成員資料表 範例資料 */
CREATE TABLE IF NOT EXISTS `Family` (
  `FamilyId` varchar(100) NOT NULL,
  `FamilyName` varchar(10) DEFAULT NULL,
  `FamilySex` varchar(10) DEFAULT NULL,
  `BirthDate` datetime DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `Family` (`FamilyId`, `FamilyName`, `FamilySex`, `BirthDate`, `PhoneNumber`) VALUES
	('34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '泰肝', '女生', '2007-10-11 00:00:00', ''),
	('3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '泰熱', '男生', '2001-12-10 00:00:00', '0934567890'),
	('91b18f1f-4ef8-4066-97c4-28daea585db5', '泰胖', '女生', '2003-05-13 00:00:00', ''),
	('91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '泰瘦', '女生', '1985-07-10 00:00:00', '0944623456'),
	('bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '泰冷', '男生', '1995-01-23 00:00:00', '0977654258'),
	('de8cb5db-2061-4d35-a662-ba5f528fadba', '泰賢', '男生', '1977-02-03 00:00:00', '0944589456');

SQL Server 的範例資料 SQL，有需要的人可自行取用：

/* 家庭排班表 SQL Server範例資料SQL */

CREATE TABLE [dbo].[CleanSchedule](
	[CleanScheduleId] [nvarchar](100) NOT NULL,
	[CleanDateTime] [datetime] NULL,
	[FamilyId] [nvarchar](100) NULL,
	[CleanItem] [nvarchar](10) NULL,
 CONSTRAINT [PK_CleanSchedule] PRIMARY KEY CLUSTERED 
(
	[CleanScheduleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT [dbo].[CleanSchedule] ([CleanScheduleId], [CleanDateTime], [FamilyId], [CleanItem]) 
VALUES  (N'0FF1B602-580C-4416-AF25-7C0876EEE19D', CAST(N'2019-08-17 00:00:00.000' AS DateTime), N'3ad93ba4-c799-4a32-ac2e-8abc74dd6375', N'1'),
	    (N'1506BC60-C614-4643-B950-8D5F803C13D6', CAST(N'2019-08-17 00:00:00.000' AS DateTime), N'91dcde4b-10b3-421e-ab8e-bb6bc23b4350', N'2'),
	    (N'24653640-14FA-49A2-AC46-DB344719A88B', CAST(N'2019-08-15 00:00:00.000' AS DateTime), N'91dcde4b-10b3-421e-ab8e-bb6bc23b4350', N'4'),
        (N'2D682CA3-B900-41B0-9AD9-5611296DFBE5', CAST(N'2019-08-18 00:00:00.000' AS DateTime), N'34bf1b6f-191d-40e9-9a8c-3c282e6a700d', N'2'),
		(N'34931A84-85E7-4236-B1C4-01D190EEFE27', CAST(N'2019-08-16 00:00:00.000' AS DateTime), N'91b18f1f-4ef8-4066-97c4-28daea585db5', N'0'),
		(N'380A9221-4A2D-41A3-A350-D53175B670C0', CAST(N'2019-08-15 00:00:00.000' AS DateTime), N'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', N'3'),
		(N'39832DFE-B93F-45D9-99C2-5EEAE76FB5F3', CAST(N'2019-08-17 00:00:00.000' AS DateTime), N'34bf1b6f-191d-40e9-9a8c-3c282e6a700d', N'0'),
		(N'4B4E212B-C40B-4233-B3B3-3531D6FE7915', CAST(N'2019-08-18 00:00:00.000' AS DateTime), N'3ad93ba4-c799-4a32-ac2e-8abc74dd6375', N'1'),
		(N'4C534822-2091-4D9F-94FB-FDCB6568E325', CAST(N'2019-08-15 00:00:00.000' AS DateTime), N'3ad93ba4-c799-4a32-ac2e-8abc74dd6375', N'5'),
		(N'61ED1DE9-5523-4FBA-B5AA-236007479849', CAST(N'2019-08-15 00:00:00.000' AS DateTime), N'de8cb5db-2061-4d35-a662-ba5f528fadba', N'0'),
		(N'67359081-D70B-4DCE-8011-B796B7516CE3', CAST(N'2019-08-17 00:00:00.000' AS DateTime), N'91b18f1f-4ef8-4066-97c4-28daea585db5', N'5'),
		(N'71CBD806-D0E4-4E87-8ACF-1BF9995EF69C', CAST(N'2019-08-16 00:00:00.000' AS DateTime), N'de8cb5db-2061-4d35-a662-ba5f528fadba', N'1'),
		(N'77B08241-1BEA-4550-AD28-B10CC4E39E35', CAST(N'2019-08-17 00:00:00.000' AS DateTime), N'de8cb5db-2061-4d35-a662-ba5f528fadba', N'4'),
		(N'78E9F96C-4B60-4ACB-910B-AC6E48AE0270', CAST(N'2019-08-17 00:00:00.000' AS DateTime), N'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', N'3'),
		(N'7BE1E533-FDE3-40EA-9A4F-E7638BA1E168', CAST(N'2019-08-18 00:00:00.000' AS DateTime), N'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', N'5'),
		(N'83595DE1-12FE-453D-9DDD-6ED7D0A355F4', CAST(N'2019-08-18 00:00:00.000' AS DateTime), N'91dcde4b-10b3-421e-ab8e-bb6bc23b4350', N'4'),
		(N'843D27D9-CB11-446B-AF65-563C641D872B', CAST(N'2019-08-16 00:00:00.000' AS DateTime), N'3ad93ba4-c799-4a32-ac2e-8abc74dd6375', N'3'),
		(N'9743E8D4-596A-4F0F-85B1-9533ED193784', CAST(N'2019-08-15 00:00:00.000' AS DateTime), N'91b18f1f-4ef8-4066-97c4-28daea585db5', N'2'),
		(N'C5DDA457-9C99-42A1-B9AD-271C9DD974F9', CAST(N'2019-08-15 00:00:00.000' AS DateTime), N'34bf1b6f-191d-40e9-9a8c-3c282e6a700d', N'1'),
		(N'C78F99F6-37B6-4FD5-AD04-D586BF032D1C', CAST(N'2019-08-16 00:00:00.000' AS DateTime), N'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', N'5'),
		(N'D420FFDD-6BAE-435F-85AE-57D917EA63ED', CAST(N'2019-08-18 00:00:00.000' AS DateTime), N'91b18f1f-4ef8-4066-97c4-28daea585db5', N'3'),
		(N'F802E6A4-5F10-4985-BFD2-0D5C4435EF6A', CAST(N'2019-08-18 00:00:00.000' AS DateTime), N'de8cb5db-2061-4d35-a662-ba5f528fadba', N'0'),
		(N'FA6FC631-97B1-4A9E-807D-2B520ACB7D28', CAST(N'2019-08-16 00:00:00.000' AS DateTime), N'91dcde4b-10b3-421e-ab8e-bb6bc23b4350', N'2'),
		(N'FF257219-DB3E-4CBB-8E63-C5A85B09950A', CAST(N'2019-08-16 00:00:00.000' AS DateTime), N'34bf1b6f-191d-40e9-9a8c-3c282e6a700d', N'4')
        
/* 家庭成員資料表 範例資料 */
CREATE TABLE [dbo].[Family](
	[FamilyId] [nvarchar](100) NOT NULL,
	[FamilyName] [nvarchar](10) NULL,
	[FamilySex] [nvarchar](10) NULL,
	[BirthDate] [datetime] NULL,
	[PhoneNumber] [nvarchar](20) NULL,
 CONSTRAINT [PK_Family] PRIMARY KEY CLUSTERED 
(
	[FamilyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT [dbo].[Family] ([FamilyId], [FamilyName], [FamilySex], [BirthDate], [PhoneNumber]) 
VALUES  (N'34bf1b6f-191d-40e9-9a8c-3c282e6a700d', N'泰肝', N'女生', CAST(N'2007-10-11 00:00:00.000' AS DateTime), NULL),
		(N'3ad93ba4-c799-4a32-ac2e-8abc74dd6375', N'泰熱', N'男生', CAST(N'2001-12-10 00:00:00.000' AS DateTime), N'0934567890'),
		(N'91b18f1f-4ef8-4066-97c4-28daea585db5', N'泰胖', N'女生', CAST(N'2003-05-13 00:00:00.000' AS DateTime), NULL),
		(N'91dcde4b-10b3-421e-ab8e-bb6bc23b4350', N'泰瘦', N'女生', CAST(N'1985-07-10 00:00:00.000' AS DateTime), N'0944623456'),
		(N'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', N'泰冷', N'男生', CAST(N'1995-01-23 00:00:00.000' AS DateTime), N'0977654258'),
		(N'de8cb5db-2061-4d35-a662-ba5f528fadba', N'泰賢', N'男生', CAST(N'1977-02-03 00:00:00.000' AS DateTime), N'0944589456')

四、思路過程

思路 #1：查詢排班表當中的所有日期

/* 第一步　查詢排班表當中的所有日期  */
SELECT CAST(CleanDateTime AS DATE) AS 打掃日期
FROM   CleanSchedule;
打掃日期
2019-08-17
2019-08-17
2019-08-15
2019-08-18
2019-08-16
2019-08-15
2019-08-17
2019-08-18
2019-08-15
2019-08-15
2019-08-17
2019-08-16
2019-08-17
2019-08-17
2019-08-18
2019-08-18
2019-08-16
2019-08-15
2019-08-15
2019-08-16
2019-08-18
2019-08-18
2019-08-16
2019-08-16

思路 #2：使用DISTINCT 剔除重複的日期

/* 第二步： 使用DISTINCT 剔除重複的日期*/
SELECT DISTINCT CAST(CleanDateTime AS DATE) AS 打掃日期
FROM   CleanSchedule
ORDER BY 打掃日期;
打掃日期
2019-08-15
2019-08-16
2019-08-17
2019-08-18

思路 #3：將「每一個日期」帶入「子查詢」，查詢每一天的「打掃」、「拖地」、「洗碗」、「倒垃圾」的負責成員

/* 第三步：將「每一個日期」帶入「子查詢」，查詢每一天的「打掃」、「拖地」、「洗碗」、「倒垃圾」的負責成員   */
SELECT DISTINCT  CAST(AllCleanScheduleDateTable.CleanDateTime AS DATE) AS 打掃日期 ,
       ( SELECT FamilyId
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
                SubQueryTable.CleanItem = '0') AS  當日打掃成員,
       ( SELECT FamilyId
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
                SubQueryTable.CleanItem = '1') AS  當日拖地成員,
       ( SELECT FamilyId
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
                SubQueryTable.CleanItem = '2') AS  當日洗碗成員,
       ( SELECT FamilyId
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
                SubQueryTable.CleanItem = '3') AS  當日倒垃圾成員
FROM CleanSchedule as AllCleanScheduleDateTable
ORDER BY 打掃日期;
打掃日期 當日打掃成員 當日拖地成員 當日洗碗成員 當日倒垃圾成員
2019-08-15 de8cb5db-2061-4d35-a662-ba5f528fadba 34bf1b6f-191d-40e9-9a8c-3c282e6a700d 91b18f1f-4ef8-4066-97c4-28daea585db5 bce0e4ae-ac70-4131-aa6f-d1e25b87fad9
2019-08-16 91b18f1f-4ef8-4066-97c4-28daea585db5 de8cb5db-2061-4d35-a662-ba5f528fadba 91dcde4b-10b3-421e-ab8e-bb6bc23b4350 3ad93ba4-c799-4a32-ac2e-8abc74dd6375
2019-08-17 34bf1b6f-191d-40e9-9a8c-3c282e6a700d 3ad93ba4-c799-4a32-ac2e-8abc74dd6375 91dcde4b-10b3-421e-ab8e-bb6bc23b4350 bce0e4ae-ac70-4131-aa6f-d1e25b87fad9
2019-08-18 de8cb5db-2061-4d35-a662-ba5f528fadba 3ad93ba4-c799-4a32-ac2e-8abc74dd6375 34bf1b6f-191d-40e9-9a8c-3c282e6a700d 91b18f1f-4ef8-4066-97c4-28daea585db5

思路 #4：將FamilyId透過子查詢，轉換為FamilyName，得到「成員姓名」

/* 第四步：將FamilyId透過子查詢，轉換為FamilyName，得到「成員姓名」  */
SELECT DISTINCT  
       CAST(AllCleanScheduleDateTable.CleanDateTime AS DATE) AS 打掃日期 ,
       ( SELECT ( SELECT FamilyName
                 FROM Family
                 WHERE FamilyId = SubQueryTable.FamilyId)
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
               SubQueryTable.CleanItem = '0') AS  當日打掃成員,
       ( SELECT ( SELECT FamilyName
                  FROM Family
                  WHERE FamilyId = SubQueryTable.FamilyId)
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
               SubQueryTable.CleanItem = '1') AS  當日拖地成員,
       ( SELECT ( SELECT FamilyName
                  FROM Family
                  WHERE FamilyId = SubQueryTable.FamilyId)
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
               SubQueryTable.CleanItem = '2') AS  當日洗碗成員,
       ( SELECT ( SELECT FamilyName
                  FROM Family
                  WHERE FamilyId = SubQueryTable.FamilyId)
         FROM CleanSchedule AS SubQueryTable
         WHERE  SubQueryTable.CleanDateTime = AllCleanScheduleDateTable.CleanDateTime AND 
               SubQueryTable.CleanItem = '3') AS  當日倒垃圾成員
FROM CleanSchedule as AllCleanScheduleDateTable
ORDER BY 打掃日期;
打掃日期 當日打掃成員 當日拖地成員 當日洗碗成員 當日倒垃圾成員
2019-08-15 泰賢 泰肝 泰胖 泰冷
2019-08-16 泰胖 泰賢 泰瘦 泰熱
2019-08-17 泰肝 泰熱 泰瘦 泰冷
2019-08-18 泰賢 泰熱 泰肝 泰胖

五、觀念延伸閱讀：子查詢

MySQL 超新手入門（10）子查詢
http://www.codedata.com.tw/database/mysql-tutorial-9-subquery/

六、相關情境練習

使用資料表：Order 訂單表頭資料

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

題目：統計各月份三個員工的訂單業績

統計訂單表頭資料當中，員工1、員工3、員工5 在「1996年的每個月」得到的訂單數量
https://ithelp.ithome.com.tw/upload/images/20190921/20120331yAWADrquXd.png


上一篇
第四堂：家事管理 - 從計算年紀 理解 SELECT 的常客(日期函式、數學函式、型態轉換)
下一篇
第六堂：家事管理 - 最後分工日期一覽表 (FROM 子查詢 + JOIN)
系列文
從問題理解與活用SQL語法 共 30 篇
  1. 26
    第二十六堂：英文單字測驗程式 - 學生帳號資料管理(5) - 修改學生與刪除學生 (認識實體)
  2. 27
    第二十七堂：英文單字測驗程式 - 學生帳號資料管理(6) - 物件實體操作資料 (ORM、Entity Framework)
  3. 28
    第二十八堂：英文單字測驗程式 - 單字測驗 - 亂數取得單字 (LIMIT、TOP、綜合練習)
  4. 29
    第二十九堂：英文單字測驗程式 - 測驗主明細紀錄與正確率統計 (綜合練習)
  5. 30
    第三十堂：師父引進門，分手在個人 (專案開源+從與SQL的分分合合到現在開課誤人子弟)
完整目錄
圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0
一級屠豬士
iT邦大師 1 級 ‧ 2019-10-22 15:36:42 
另外一種寫法,你參考一下. 我另外把那些 UUID 改成 int了.並且用 InnoDB , 做FK.

show create table family\G
*************************** 1. row ***************************
       Table: family
Create Table: CREATE TABLE `family` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `mname` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


show create table cleansche\G
*************************** 1. row ***************************
       Table: cleansche
Create Table: CREATE TABLE `cleansche` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `cdate` date NOT NULL,
  `fid` tinyint(3) unsigned NOT NULL,
  `citem` char(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fid` (`fid`),
  CONSTRAINT `cleansche_ibfk_1` FOREIGN KEY (`fid`) REFERENCES `family` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

--
select *
  from family;

+----+--------+
| id | mname  |
+----+--------+
|  1 | 泰肝   |
|  2 | 泰熱   |
|  3 | 泰胖   |
|  4 | 泰瘦   |
|  5 | 泰冷   |
|  6 | 泰賢   |
+----+--------+
6 rows in set (0.00 sec)

select *
  from cleansche;

+----+------------+-----+-------+
| id | cdate      | fid | citem |
+----+------------+-----+-------+
|  1 | 2019-08-17 |   2 | 1     |
|  2 | 2019-08-17 |   4 | 2     |
|  3 | 2019-08-15 |   4 | 4     |
|  4 | 2019-08-18 |   1 | 2     |
|  5 | 2019-08-16 |   3 | 0     |
|  6 | 2019-08-15 |   5 | 3     |
|  7 | 2019-08-17 |   1 | 0     |
|  8 | 2019-08-18 |   2 | 1     |
|  9 | 2019-08-15 |   2 | 5     |
| 10 | 2019-08-15 |   6 | 0     |
| 11 | 2019-08-17 |   3 | 5     |
| 12 | 2019-08-16 |   6 | 1     |
| 13 | 2019-08-17 |   6 | 4     |
| 14 | 2019-08-17 |   5 | 3     |
| 15 | 2019-08-18 |   5 | 5     |
| 16 | 2019-08-18 |   4 | 4     |
| 17 | 2019-08-16 |   2 | 3     |
| 18 | 2019-08-15 |   3 | 2     |
| 19 | 2019-08-15 |   1 | 1     |
| 20 | 2019-08-16 |   5 | 5     |
| 21 | 2019-08-18 |   3 | 3     |
| 22 | 2019-08-18 |   6 | 0     |
| 23 | 2019-08-16 |   4 | 2     |
| 24 | 2019-08-16 |   1 | 4     |
+----+------------+-----+-------+
24 rows in set (0.00 sec)

---

select cdate
     , f0.mname as it0name
     , f1.mname as it1name
     , f2.mname as it2name
     , f3.mname as it3name
  from (select cdate
             , group_concat(if(citem='0',fid, null)) as item0
             , group_concat(if(citem='1',fid, null)) as item1
             , group_concat(if(citem='2',fid, null)) as item2
             , group_concat(if(citem='3',fid, null)) as item3
         from cleansche
        group by cdate) a
     , family f0
     , family f1
     , family f2
     , family f3
 where f0.id = item0
   and f1.id = item1
   and f2.id = item2
   and f3.id = item3
 order by cdate;
+------------+---------+---------+---------+---------+
| cdate      | it0name | it1name | it2name | it3name |
+------------+---------+---------+---------+---------+
| 2019-08-15 | 泰賢    | 泰肝    | 泰胖    | 泰冷    |
| 2019-08-16 | 泰胖    | 泰賢    | 泰瘦    | 泰熱    |
| 2019-08-17 | 泰肝    | 泰熱    | 泰瘦    | 泰冷    |
| 2019-08-18 | 泰賢    | 泰熱    | 泰肝    | 泰胖    |
+------------+---------+---------+---------+---------+
4 rows in set (0.00 sec)

-- 還有上面方法的變化
select cdate
     , group_concat(if(citem='0',mname, null)) as it0name
     , group_concat(if(citem='1',mname, null)) as it1name
     , group_concat(if(citem='2',mname, null)) as it2name
     , group_concat(if(citem='3',mname, null)) as it3name
  from cleansche c
  join family f
    on c.fid = f.id
 group by cdate
 order by cdate;

+------------+---------+---------+---------+---------+
| cdate      | it0name | it1name | it2name | it3name |
+------------+---------+---------+---------+---------+
| 2019-08-15 | 泰賢    | 泰肝    | 泰胖    | 泰冷    |
| 2019-08-16 | 泰胖    | 泰賢    | 泰瘦    | 泰熱    |
| 2019-08-17 | 泰肝    | 泰熱    | 泰瘦    | 泰冷    |
| 2019-08-18 | 泰賢    | 泰熱    | 泰肝    | 泰胖    |
+------------+---------+---------+---------+---------+
4 rows in set (0.00 sec)
登入發表回應

我要留言

立即登入留言
iThome鐵人賽
參賽組數
902 組
團體組數
37 組
累計文章數
19750 篇
完賽人數
529 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 17th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# linux windows server css react
熱門問題
熱門回答
熱門文章
IT邦幫忙